﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.Caching;
using Microsoft.ApplicationBlocks.Data;

namespace maskx.JqGridHelper
{
    public class ComplexSearch
    {
        static double CacheMinuteTime = 10;
       
        public static string Search()
        {
            StringBuilder sb = new StringBuilder();
            string connstr = ConfigHelper.ConnectionString;
            string cachekey = string.Empty;

            #region viewname
            string viewname = HttpContext.Current.Request["qn"];
            if (string.IsNullOrEmpty(viewname))
                return string.Empty;
            var safeviewname = JqGridSQLHelper.SafeObjectName(viewname);
            cachekey += viewname;
            #endregion


            #region cols
            string cols = string.Empty;
            int colindex = 0;
            if (int.TryParse(HttpContext.Current.Request["coli"], out colindex))
            {
                var colnames = JqGridSQLHelper.GetColnames(connstr, viewname);
                for (int i = 0; i < colindex; i++)
                {
                    cols += safeviewname + "." + JqGridSQLHelper.SafeObjectName(colnames[i]) + ",";
                    cachekey += colnames[i];
                }
                cols = cols.TrimEnd(',');
            }
            else
            {
                cols = safeviewname + ".*";
            }
            #endregion

            #region idcol
            string idcol = "id";
            if (!string.IsNullOrEmpty(HttpContext.Current.Request["idcol"]))
            {
                idcol = HttpContext.Current.Request["idcol"];
                cachekey += idcol;
            }
            #endregion

            JqGridPars pars = new JqGridPars(HttpContext.Current.Request.Params);
            List<SqlParameter> sqlpars = new List<SqlParameter>();
            sqlpars.Add(new SqlParameter("page", pars.page));
            sqlpars.Add(new SqlParameter("rows", pars.rows));
            cachekey += pars.rows;
            SqlParameter parTotalRecord = new SqlParameter("totalRecord", 0);
            parTotalRecord.Direction = ParameterDirection.Output;
            sqlpars.Add(parTotalRecord);
            SqlParameter partotalPage = new SqlParameter("totalPage", 0);
            partotalPage.Direction = ParameterDirection.Output;
            sqlpars.Add(partotalPage);

            #region where rules
            string condition = string.Empty;
            if (pars._search)
            {
                condition = JqGridSQLHelper.BuildQueryCondition(pars, sqlpars);
                if (!string.IsNullOrEmpty(condition))
                {
                    condition = " where " + condition;
                }
            }
            cachekey += pars.searchField + pars.searchOper + pars.searchString;
            if(pars.filters!=null)
                cachekey += pars.filters.JsonString;
            #endregion

            #region order rules
            string order = JqGridSQLHelper.BuildQueryOrder(safeviewname, pars);
            if (string.IsNullOrEmpty(order))
            {
                order = idcol;
            }
            cachekey += order;
            #endregion

            if (pars.page == 1 && CacheMinuteTime > 0)
            {
                var cacheData = HttpContext.Current.Cache[cachekey];
                if (cacheData != null)
                    return cacheData.ToString();
            }
            string cmd = string.Empty;
            string m = "1";
            if (!string.IsNullOrEmpty(HttpContext.Current.Request["idcol"]))
            {
                m=HttpContext.Current.Request["idcol"];
            }
            if (m == "1")
            {
                cmd = string.Format(cmdTemplete1,
                        safeviewname,
                        condition,
                        order,
                        JqGridSQLHelper.SafeObjectName(idcol),
                        cols
                        );
                idcol = "rowid";
            }
            else
            {
                cmd = string.Format(cmdTemplete,
                       safeviewname,
                       condition,
                       order,
                       JqGridSQLHelper.SafeObjectName(idcol),
                       cols
                       );
            }
            using (var reader = SqlHelper.ExecuteReader(connstr, CommandType.Text, cmd, sqlpars.ToArray()))
            {
                sb = JSONHelper.WriteRowJsonWithColName(idcol, reader);
            }
            string g = "{{\"page\":\"{0}\",\"total\":\"{1}\",\"records\":\"{2}\",{3}";

            var rtv = string.Format(g, pars.page, partotalPage.Value, parTotalRecord.Value, sb.Remove(0, 1));
            if (pars.page == 1 && CacheMinuteTime > 0)
            {
                HttpContext.Current.Cache.Insert(cachekey, rtv, null, DateTime.Now.AddMinutes(CacheMinuteTime), TimeSpan.Zero);
            }
            return rtv;
        }

        #region SQL command templete
        const string cmdTemplete = @"
SET NOCOUNT ON;

select @totalRecord=COUNT(*)
from {0}
{1}
SELECT @totalPage=CEILING((@totalRecord+0.0)/@rows)

DECLARE @StartRecord int 
DECLARE @EndRecord int
     
SET @StartRecord = (@page-1)*@rows
SET @EndRecord = @StartRecord + @rows+1;

select t.rowid,{4}
from {0}
	inner join (
        select  {0}.{3},ROW_NUMBER() over(order by {2}) as rowid 
        from {0} 
        {1}) as t 
		    on t.{3}={0}.{3}
		    and t.rowid>@StartRecord 
		    and t.rowid<@EndRecord 
order by t.rowid
";
        #endregion

        #region SQL command templete1
        const string cmdTemplete1 = @"
SET NOCOUNT ON;

select rowid=identity(int,1,1),{4} into #t from {0} {1} order by {2}

select @totalRecord=@@ROWCOUNT
SELECT @totalPage=CEILING((@totalRecord+0.0)/@rows)

DECLARE @StartRecord int 
DECLARE @EndRecord int
     
SET @StartRecord = (@page-1)*@rows
SET @EndRecord = @StartRecord + @rows+1;

select * from #t as t where t.rowid>@StartRecord   and t.rowid<@EndRecord 
drop table #t
";
        #endregion
    }
}
